package com.xt.base.util;

import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.http.HttpServletResponse;

/**
 * @author ��鲨
 * @create-date:2008-4-14
 * @description:
 */

public class DealBlob{
	
	private String tableName=null;		//����
	private String blobFieldName=null;	//Blob�ֶ���
	private String idFieldName=null;	//id�ֶ���
	private String otherFieldName=null; //�����ֶ����
	private String otherFieldValue=null;
	private String dateFieldName=null;	//ʱ���ֶ����
	private String dateFieldValue=null;
	
	/**
	 * ���캯��-���û����
	 * @param tableName			����
	 * @param blobFieldName		bolb�ֶ���
	 * @param idFieldName		������ֶ�ʱ��","����
	 */
	public DealBlob(String tableName,String blobFieldName,String idFieldName){
		this.tableName=tableName;
		this.blobFieldName=blobFieldName;
		this.idFieldName=idFieldName;
	}	
	
	/**
	 * ���������ֶΡ���������ֵ�ͣ�����ֶ�ʱ��","����
	 * @param otherFieldName	String
	 */
	public void setOtherFieldName(String otherFieldName){
		this.otherFieldName=otherFieldName;
	}
	
	/**
	 * ���������ֶε�ֵ����������ֵ���ֶ�ֵ������ֶ�ʱ��","����
	 * @param otherFieldValue
	 */
	public void setOtherFieldValue(String otherFieldValue){
		this.otherFieldValue=otherFieldValue;
	}
	
	/**
	 * ����ʱ���ֶΣ�����ֶ�ʱ��","����
	 * @param dateFieldName		String
	 */
	public void setDateFieldName(String dateFieldName){
		this.dateFieldName=dateFieldName;
	}
	
	/**
	 * ����ʱ���ֶε�ֵ�����ַ��ʽ��ʱ��ֵ������ֶ�ʱ��","����
	 * @param dateFieldValue
	 */
	public void setDateFieldValue(String dateFieldValue){
		this.dateFieldValue=dateFieldValue;
	}
	
	/**
	 * ����Blob
	 * @param conn 	��ݿ�����
	 * @param in	InputStream	
	 * @param id    ������ֶ�ʱ��","����
	 * @return boolean true-����ɹ� false-����ʧ��
	 * @throws Exception
	 */
	public boolean dealBlob(Connection conn,InputStream in,String id) throws Exception{
		String insertSql=null;		//�������
		String querySql=null;		//��ѯ���
		String updateSql=null;		//�������
		String sqlWhere=null;		//��ѯ����
		
		if(id==null){
			throw new RuntimeException("δ����id�ֶζ�Ӧ��ֵ��");
		}
		String[] idNameArr=idFieldName.split(",");
		String[] idValueArr=id.split(",");
		if(idNameArr.length!=idValueArr.length){
			throw new RuntimeException("id�ֶε��ֶθ�����ֵ�ĸ���ƥ�䣡");
		}
		sqlWhere=" 1=1";
		for(int i=0;i<idNameArr.length;i++){
			sqlWhere+=" and "+idNameArr[i]+"='"+idValueArr[i]+"'";
		}
		
		if(otherFieldName==null&&dateFieldName==null){		//û�������ֶκ�ʱ���ֶ�
			insertSql="insert into "+tableName+"("+idFieldName+","+blobFieldName+") "
					 +"values('"+id.replaceAll(",", "','")+"',empty_blob())";//�������
			querySql="select "+blobFieldName+" from "+tableName+" where "+sqlWhere+" for update";//��ѯ���
			updateSql="update "+tableName+" set "+blobFieldName+"=? where "+sqlWhere;//�������
		}else if(otherFieldName!=null&&dateFieldName==null){	//�������ֶΣ���ʱ���ֶ�
			if(otherFieldValue==null){
				throw new RuntimeException("δ���������ֶζ�Ӧ��ֵ��");
			}
			String[] otherNameArr=otherFieldName.split(",");
			String[] otherValueArr=otherFieldValue.split(",");
			if(otherNameArr.length!=otherValueArr.length){
				throw new RuntimeException("�����ֶε��ֶθ�����ֵ�ĸ���ƥ�䣡");
			}
			insertSql="insert into "+tableName+"("+idFieldName+","+blobFieldName+","+otherFieldName+") "
					 +"values('"+id.replaceAll(",", "','")+"',empty_blob(),'"+otherFieldValue.replaceAll(",", "','")+"')";//������Ϣ
			querySql="select "+blobFieldName+" from "+tableName+" where "+sqlWhere+" for update";//��ѯ���
			updateSql="update "+tableName+" set "+blobFieldName+"=? where "+sqlWhere;//�������
		}else if(otherFieldName==null&&dateFieldName!=null){	//��ʱ���ֶΣ��������ֶ�
			if(dateFieldName==null){
				throw new RuntimeException("δ����ʱ���ֶζ�Ӧ��ֵ��"); 
			}
			String[] dateNameArr=dateFieldName.split(",");
			String[] dateValueArr=dateFieldValue.split(",");
			if(dateNameArr.length!=dateValueArr.length){
				throw new RuntimeException("ʱ���ֶε��ֶθ�����ֵ�ĸ���ƥ�䣡");
			}
			dateFieldValue="";
			for(int i=0;i<dateValueArr.length;i++){
				if(dateValueArr[i].length()==10){
					dateFieldValue+="to_date('"+dateValueArr[i]+"','yyyy-MM-dd'),";
				}else if(dateValueArr[i].length()==19){
					dateFieldValue+="to_date('"+dateValueArr[i]+"','yyyy-MM-dd hh24:mi:ss'),";
				}else if(dateValueArr[i].equals("sysdate")||dateValueArr[i].equals("SYSDATE")){
					dateFieldValue+="sysdate,";
				}else{
					throw new RuntimeException("ʱ���ʽ����ȷ��");
				}
			}
			dateFieldValue=dateFieldValue.substring(0,dateFieldValue.length()-1);
			insertSql="insert into "+tableName+"("+idFieldName+","+blobFieldName+","+dateFieldName+") "
					 +"values('"+id.replaceAll(",", "','")+"',empty_blob(),"+dateFieldValue+")";//������Ϣ
			querySql="select "+blobFieldName+" from "+tableName+" where "+sqlWhere+" for update";//��ѯ���
			updateSql="update "+tableName+" set "+blobFieldName+"=? where "+sqlWhere;//�������
		}else{		//���������ֶ�����ʱ���ֶ�
			if(otherFieldValue==null){
				throw new RuntimeException("δ���������ֶζ�Ӧ��ֵ��");
			}
			if(dateFieldValue==null){
				throw new RuntimeException("δ����ʱ���ֶζ�Ӧ��ֵ��");
			}
			String[] otherNameArr=otherFieldName.split(",");
			String[] otherValueArr=otherFieldValue.split(",");
			if(otherNameArr.length!=otherValueArr.length){
				throw new RuntimeException("�����ֶε��ֶθ�����ֵ�ĸ���ƥ�䣡");
			}
			String[] dateNameArr=dateFieldName.split(",");
			String[] dateValueArr=dateFieldValue.split(",");
			if(dateNameArr.length!=dateValueArr.length){
				throw new RuntimeException("ʱ���ֶε��ֶθ�����ֵ�ĸ���ƥ�䣡");
			}
			dateFieldValue="";
			for(int i=0;i<dateValueArr.length;i++){
				if(dateValueArr[i].length()==10){
					dateFieldValue+="to_date('"+dateValueArr[i]+"','yyyy-MM-dd'),";
				}else if(dateValueArr[i].length()==19){
					dateFieldValue+="to_date('"+dateValueArr[i]+"','yyyy-MM-dd hh24:mi:ss'),";
				}else if(dateValueArr[i].equals("sysdate")||dateValueArr[i].equals("SYSDATE")){
					dateFieldValue+="sysdate,";
				}else{
					throw new RuntimeException("ʱ���ʽ����ȷ��");
				}
			}
			dateFieldValue=dateFieldValue.substring(0,dateFieldValue.length()-1);
			insertSql="insert into "+tableName+"("+idFieldName+","+blobFieldName+","+otherFieldName+","+dateFieldName+") "
					 +"values('"+id.replaceAll(",", "','")+"',empty_blob(),'"+otherFieldValue.replaceAll(",", "','")+"',"+dateFieldValue+")";//������Ϣ
			querySql="select "+blobFieldName+" from "+tableName+" where "+sqlWhere+" for update";//��ѯ���
			updateSql="update "+tableName+" set "+blobFieldName+"=? where "+sqlWhere;//�������
		}
		boolean bl=true;
		Blob blob=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(insertSql);
			ps.execute();
			ps.close();
			
			ps = conn.prepareStatement(querySql);
			rs = ps.executeQuery();
			if(rs.next()){
			   blob = rs.getBlob(1);
			}
			rs.close();
			ps.close();
			OffInfo.inputStream2Blob(in, blob);//��InputStream�е�����д��Blob
			
			ps = conn.prepareStatement(updateSql);
			ps.setBlob(1, blob);
		    ps.execute();
		    ps.close();
		}catch(Exception e){
			bl=false;
			throw e;
		}
		return bl;
	}
	
	/**
	 * ����Blob�е����
	 * @param conn	��ݿ�����
	 * @param in	InputStream,Ϊnullʱ���Blob�е����
	 * @param id	������ֶ�ʱ��","����
	 * @return	boolean true-���³ɹ� false-����ʧ��
	 * @throws Exception
	 */
	public boolean updateBlob(Connection conn,InputStream in,String id) throws Exception{
		if(id==null){
			throw new RuntimeException("δ����id�ֶζ�Ӧ��ֵ��");
		}
		String[] idNameArr=idFieldName.split(",");
		String[] idValueArr=id.split(",");
		if(idNameArr.length!=idValueArr.length){
			throw new RuntimeException("id�ֶε��ֶθ�����ֵ�ĸ���ƥ�䣡");
		}
		String sqlWhere=" 1=1";
		for(int i=0;i<idNameArr.length;i++){
			sqlWhere+=" and "+idNameArr[i]+"='"+idValueArr[i]+"'";
		}
		
		String updateSql=null;
		boolean bl=true;
		PreparedStatement ps = null;
		try{		
			if(in==null){
				updateSql="update "+tableName+" set "+blobFieldName+"=empty_blob() where "+sqlWhere;
				ps=conn.prepareStatement(updateSql);
			}else{
				Blob blob=null;
				String querySql="select "+blobFieldName+" from "+tableName+" where "+sqlWhere+" for update";
				ps=conn.prepareStatement(querySql);
				ResultSet rs=ps.executeQuery();
				if(rs.next()){
					blob=rs.getBlob(1);
					OffInfo.inputStream2Blob(in, blob);
					ps.close();
					
					updateSql="update "+tableName+" set "+blobFieldName+"=? where "+sqlWhere;
					ps=conn.prepareStatement(updateSql);
					ps.setBlob(1, blob);
				}
				rs.close();
			}
			ps.execute();
			ps.close();
		}catch(Exception e){
			bl=false;
			throw e;
		}
		return bl;
	}
	
	/**
	 * ��ѯBlob���Ը����ķ�ʽ��servlet���������ʽ��servlet�����
	 * @param conn		��ݿ�����
	 * @param id		Ψһ���
	 * @param fileName	�ļ���
	 * @param docType	�ļ�����
	 * @param response	HttpServletResponse
	 * @throws Exception
	 */
	public void blobToServlet(Connection conn,String id,String fileName,
				String docType,HttpServletResponse response) throws Exception{
		
		if(id==null){
			throw new RuntimeException("δ����id�ֶζ�Ӧ��ֵ��");
		}
		String[] idNameArr=idFieldName.split(",");
		String[] idValueArr=id.split(",");
		if(idNameArr.length!=idValueArr.length){
			throw new RuntimeException("id�ֶε��ֶθ�����ֵ�ĸ���ƥ�䣡");
		}
		String sqlWhere=" 1=1";
		for(int i=0;i<idNameArr.length;i++){
			sqlWhere+=" and "+idNameArr[i]+"='"+idValueArr[i]+"'";
		}
		
		Blob blob=null;	
		String sql="select "+blobFieldName+" from "+tableName+" where "+sqlWhere;//��ѯ��Blob
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
			   blob = rs.getBlob(1);
			}
			rs.close();
			ps.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		if(fileName==null||"".equals(fileName)){//������ķ�ʽ���
			OffInfo.writeBlobToServlet(blob, docType, response);
		}else{//������ʽ���
			OffInfo.writeBlobToServletByAttachment(blob, docType, fileName, response);
		}
		
	}
	
	
}
